﻿using System.Data;
using SemiCASE.DataAccess;
using SemiCASE.DataType.Common.Attributes;
using System;

namespace SemiCASE.Business
{
    [ServiceConnectionNameAttribute("SemiCASEConnectionString")]
    public class MainSuccessBS : BusinessBase
    {
        public DataTable LoadMSSFromDB(Guid pProjectID, Guid pUseCaseID)
        {
            DataTable dt = new DataTable();
            IData data = GetDataObject();
            string sqlText = @"WITH PROJECT_MSS AS
                            (
	                            SELECT DISTINCT 
			                            [MssID]
	                            FROM [SemiCaseDB].[dbo].[RELATION]
	                            WHERE ProjectID = @ProjectID AND UseCaseID = @UseCaseID
                            )

                           SELECT 
	                            [Order] AS SiraNo
                                , [MssID]
                                , [Name]
                            FROM [SemiCaseDB].[dbo].[MSS]
                            WHERE MssID IN (SELECT * FROM PROJECT_MSS)
                            ORDER BY [Order] ASC";

            data.AddSqlParameter("ProjectID", pProjectID, SqlDbType.UniqueIdentifier);
            data.AddSqlParameter("UseCaseID", pUseCaseID, SqlDbType.UniqueIdentifier);
            data.GetRecords(dt, sqlText);
            return dt;
        }

        public void CreateMSS(Guid pProjectID, Guid pUseCaseID, DataTable dt)
        {
            if (dt == null)
                return;

            foreach (DataRow row in dt.Rows)
            {
                IData data = GetDataObject();
                string sqlText = @"INSERT INTO [SemiCaseDB].[dbo].[MSS]
                                       ([MssID]
                                       ,[Order]
                                       ,[Name])
                                 VALUES
                                       (@MssID
                                       ,@Order
                                       ,@Name);

                                INSERT INTO [SemiCaseDB].[dbo].[RELATION]
                                       ([RelationID]
                                       ,[ProjectID]
                                       ,[UseCaseID]
                                       ,[MssID])
                                 VALUES
                                       (NEWID()
                                       ,@ProjectID
                                       ,@UseCaseID
                                       ,@MssID)";

                data.AddSqlParameter("ProjectID", pProjectID, SqlDbType.UniqueIdentifier);
                data.AddSqlParameter("UseCaseID", pUseCaseID, SqlDbType.UniqueIdentifier);
                data.AddSqlParameter("MssID", row["MssID"], SqlDbType.UniqueIdentifier);
                data.AddSqlParameter("Order", row["SiraNo"], SqlDbType.VarChar);
                data.AddSqlParameter("Name", row["Name"], SqlDbType.VarChar);

                data.ExecuteStatement(sqlText);
            }
        }
    }
}
